if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetWIPProtocolProcessIds]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetWIPProtocolProcessIds]
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO      
/******************************************************************            
* Name:   GetWIPProtocolProcessIds            
* Purpose:  Retrieves the details(from multiple tables using joins) according to the filter parameters            
*            
* PARAMETERS            
* Name        Description                 
* -------------    -------------------------------------------            
* @domainUserId           Domain User ID (REQUIRED)              
* @protocolNumber   Protocol number tied up with a protocol            
* @protocolTitle   Title for the protocol            
* @pIId     PI Person Id            
* @organizationIds   Organization for a protocol            
* @sortColumn varchar(150) = 'SubmittedDate',          
* @sortOrder bit = 1-->Asc, 0-->Desc           
******************************************************************/            
CREATE PROCEDURE [dbo].[GetWIPProtocolProcessIds] --103100,'H',null,null,null,null,'LastModifiedBy'        
(            
@domainUserId int,        
@moduleType char(1) = 'H',            
@protocolNumber  VARCHAR(12)=null,            
@protocolTitle VARCHAR(1024)=null,            
@pIId INT=null,            
@organizationIds VARCHAR(4000)=null,          
@sortColumn varchar(150) = 'ProtocolNumber',          
@sortOrder bit = 1-->Asc, 0-->Desc         
          
)            
            
AS BEGIN            
            
SET NOCOUNT ON            
            
 if(@protocolNumber is null)            
  Set @protocolNumber='%'            
 else            
  Set @protocolNumber=@protocolNumber+'%'             
             
 if(@protocolTitle is null)            
  Set @protocolTitle='%'            
 else            
  Set @protocolTitle=@protocolTitle+'%'             
            
             
  DECLARE @protocolresourcetypeid varchar(2)            
  set @protocolresourcetypeid = (select [id] from FwkResourceType where [Name] = 'Protocol')            
            
  declare @keyGiverAccessLevel int            
  set @keyGiverAccessLevel = 2           
          
 --The following table serves the sorting which otherwise will make the query really big          
 Declare @tempWIPProtocolProcesses table           
 (          
  ProtocolProcessId int,          
  ProtocolNumber VARCHAR(12),          
  ProtocolId int,          
  Title VARCHAR(1024),          
  ShortTitle varchar(60),          
  [ProtocolType] varchar(20),          
  Createdate Datetime,          
  LastModifiedDate DateTime,            
  LastModifiedBy Varchar(200),            
  PIName varchar(200),          
  SponsorName varchar(150),          
  RefMeaning varchar(150),          
  AccessLevel int            
 )          
            
 if (@organizationIds is null)            
            
 BEGIN            
  Insert Into @tempWIPProtocolProcesses SELECT  pp.[Id] as ProtocolProcessId,            
    p.ProtocolNumber,            
    p.[Id] as ProtocolId,            
    p.Title,            
    p.ShortTitle,            
    pp.Type,              
    pp.CreateDate,            
    pp.LastModifiedDate,          
    (rtrim(ltrim(admp.LastName)) +', '+rtrim(ltrim(admp.FirstName)) )as LastModifieBy,            
    (rtrim(ltrim(adp.LastName)) +', '+rtrim(ltrim(adp.FirstName))) as PIName,            
    (rtrim(ltrim(ads.[Name]))) as Sponsor,            
    (CASE rtrim(ltrim(pp.[Name])) WHEN '' THEN rtrim(ltrim(rfc.RefMeaning)) ELSE rtrim(ltrim(rfc.RefMeaning))+' ('+rtrim(ltrim(pp.[Name]))+')' END) as RefMeaning,             
    arl.AccessLevel            
              
   FROM Protocol p             
              
   INNER JOIN dbo.fnAdmQGetAuthorizedResourceList(@domainUserId, @protocolresourcetypeid) arl            
    on arl.resourceid = p.fwkresourceid             
   LEFT JOIN dbo.protocolProcess pp             
    ON p.[Id]=pp.ProtocolId            
   LEFT JOIN dbo.RefCode rfc            
    ON rfc.[RefCode]=pp.Type            
   LEFT JOIN dbo.ADMPerson adp            
    ON adp.[Id]=p.PIPersonId                 
   LEFT JOIN dbo.ADMPerson admp            
    ON admp.[FwkDomainUserId]=pp.LastModifiedByUserId            
              
   LEFT JOIN dbo.ADMSponsor ads            
    ON ads.[Id]=p.ADMSponsorId            
   LEFT JOIN dbo.FWKDomainOrganization FDO            
   ON FDO.[Id]=p.PIFwkDomainOrganizationId               
   WHERE             
    p.ModuleType = @moduleType          
    AND ISNULL(p.ProtocolNumber,'*')like @protocolNumber            
    AND ISNULL(p.Title, '*') like @protocolTitle            
    AND ISNULL(CAST(p.PIPersonId AS VARCHAR(20)),'*')like ISNULL(CAST(@pIID AS VARCHAR(20)), '%')            
    AND pp.Status='PPSTATUS_WIP'            
    AND P.MarkForDeletion = 0          and arl.AccessLevel !=  @keyGiverAccessLevel              
              
 END            
 ELSE            
 BEGIN            
             
   Declare @#TempCosIdTable Table([Id] int Primary Key,[Value] Varchar(4))            
   Insert Into @#TempCosIdTable  select * from GetTableOfDelimittedString(@organizationIds,'|')            
             
  Insert Into @tempWIPProtocolProcesses           
   SELECT  pp.[Id] as ProtocolProcessId,            
    p.ProtocolNumber,            
    p.[Id] as ProtocolId,            
    p.Title,            
    p.ShortTitle,            
    pp.Type,            
    pp.CreateDate,            
    pp.LastModifiedDate,          
    (rtrim(ltrim(admp.LastName)) +', '+rtrim(ltrim(admp.FirstName)) )as LastModifieBy,            
    (rtrim(ltrim(adp.LastName)) +', '+rtrim(ltrim(adp.FirstName))) as PIName,            
    (rtrim(ltrim(ads.[Name]))) as Sponsor,            
    (CASE rtrim(ltrim(pp.[Name])) WHEN '' THEN rtrim(ltrim(rfc.RefMeaning)) ELSE rfc.RefMeaning+' ('+rtrim(ltrim(pp.[Name]))+')' END) as RefMeaning,             
    arl.AccessLevel            
              
   FROM Protocol p             
              
   INNER JOIN dbo.fnAdmQGetAuthorizedResourceList(@domainUserId, @protocolresourcetypeid) arl            
    on arl.resourceid = p.fwkresourceid             
   LEFT JOIN dbo.protocolProcess pp             
    ON p.[Id]=pp.ProtocolId            
   LEFT JOIN dbo.RefCode rfc            
    ON rfc.[RefCode]=pp.Type            
   LEFT JOIN dbo.ADMPerson adp            
    ON adp.[Id]=p.PIPersonId            
   LEFT JOIN dbo.ADMPerson admp            
    ON admp.[Id]=pp.LastModifiedByUserId            
   LEFT JOIN dbo.ADMSponsor ads            
    ON ads.[Id]=p.ADMSponsorId            
   LEFT JOIN dbo.FWKDomainOrganization FDO            
    ON FDO.[Id]=p.PIFwkDomainOrganizationId            
              
   WHERE             
    p.ModuleType = @moduleType          
    AND  ISNULL(p.ProtocolNumber,'*')like @protocolNumber              
    AND ISNULL(p.Title, '*') like @protocolTitle            
    AND ISNULL(CAST(p.PIPersonId AS VARCHAR(20)),'*')like ISNULL(CAST(@pIID AS VARCHAR(20)), '%')            
    AND ISNULL(CAST(FDO.[Id]AS varchar(4)),'*')IN (Select Value from @#TempCosIdTable)            
    AND pp.Status='PPSTATUS_WIP'            
    AND P.MarkForDeletion = 0            
    AND arl.AccessLevel !=  @keyGiverAccessLevel           
               
 END           
           
 If exists (Select 1 from @tempWIPProtocolProcesses)          
  BEGIN          
   if @sortColumn = 'CreateDate' or @sortColumn = 'LastModifiedDate'            
    BEGIN          
    Select ProtocolProcessId          
     From @tempWIPProtocolProcesses          
     order by           
      case @sortOrder           
       When 0 then           
        case @SortColumn          
         when 'CreateDate' then Createdate          
         when 'LastModifiedDate' then LastmodifiedDate                                                
         end           
       End Desc,                 
      case @sortOrder          
       When 1 then          
        case @SortColumn          
         when 'CreateDate' then Createdate          
         when 'LastModifiedDate' then LastmodifiedDate                             
         end                
       END          
    END             
  ELse          
   BEGIN          
 Select ProtocolProcessId          
     From @tempWIPProtocolProcesses          
     order by           
      case @sortOrder           
       When 0 then           
        case @SortColumn          
         when 'ProtocolNumber' then ProtocolNumber          
         when 'Title' then Title          
         when 'RefMeaning' then RefMeaning          
         when 'PIName' then PIName        
   when 'lastModifiedBy' then LastModifiedBy           
         else ProtocolNumber          
         end           
       End Desc,                 
      case @sortOrder          
       When 1 then          
        case @SortColumn          
         when 'ProtocolNumber' then ProtocolNumber          
         when 'Title' then Title          
         when 'RefMeaning' then RefMeaning          
         when 'PIName' then PIName        
		 when 'lastModifiedBy' then LastModifiedBy           
         else ProtocolNumber                         
         end                
       END          
   END          
           
  END          
          
END 